<?php
require_once APPLICATION_PATH . '/../library/PHPExcel/PHPExcel.php';

class Uuch_PhpExcel {
    public static function printexcel($id)
    {
        $billall = Application_Model_wayBillItem::load($id);
        $invoiceall = Application_Model_invoice::bybillNo($billall->billNo);
        $invoicedetail = Application_Model_invoicedetail::bybillNo($billall->billNo);
        $objExcel = new PHPExcel();  
        $objWriter = new PHPExcel_Writer_Excel2007($objExcel);
        $objWriter->setOffice2003Compatibility(true); 
//        $objExcel->setActiveSheetIndex(0); 
        $objActSheet = $objExcel->getActiveSheet();  
        $objActSheet->setTitle('Sheet1');
        
        $objActSheet->getPageMargins()->setTop((0.9/2.54));
        $objActSheet->getPageMargins()->setLeft((1/2.54));
        $objActSheet->getPageMargins()->setRight((0.6/2.54));
        $objActSheet->getPageSetup()->setFitToWidth("1");
        
        $objActSheet->getColumnDimension('A')->setWidth(8); 
        $objActSheet->getColumnDimension('B')->setWidth(10); 
        $objActSheet->getColumnDimension('C')->setWidth(8); 
        $objActSheet->getColumnDimension('D')->setWidth(9); 
        $objActSheet->getColumnDimension('E')->setWidth(15); 
        
        $objActSheet->getColumnDimension('F')->setWidth(10); 
        $objActSheet->getColumnDimension('G')->setWidth(6); 
        $objActSheet->getColumnDimension('H')->setWidth(6); 
        $objActSheet->getColumnDimension('I')->setWidth(8); 
        $objActSheet->getColumnDimension('J')->setWidth(15); 
        
        $objActSheet->mergeCells('A1:J1'); 
        $objActSheet->setCellValue('A1', ' COMMERCIAL INVOICE');
        $objActSheet->getStyle('A1')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1:J1')->getFont()->setBold(TRUE);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('A1:J1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getRowDimension('1')->setRowHeight(50);
        
        $objActSheet->mergeCells('A2:C2'); 
        $objActSheet->setCellValue('A2', " INTERNATIONAL \n  AIR WAYBILL NO.");
        $objActSheet->getStyle('A2')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A2')->getFont()->setSize(10);
        $objActSheet->getStyle('A2')->getFont()->setBold(True);
        $objActSheet->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('A2:C2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('A2:C2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('A2:C2')->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('D2:J2'); 
        $objActSheet->getStyle('D2:J2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getRowDimension('2')->setRowHeight(30);
        
        $objActSheet->mergeCells('A3:C3'); 
        $objActSheet->setCellValue('A3', ' DATE OF EXPORTATION');
        $objActSheet->getStyle('A3')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A3')->getFont()->setSize(10);
        $objActSheet->getStyle('A3')->getFont()->setBold(True);
        $objActSheet->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('A3:C3')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('A3:C3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        
        $objActSheet->mergeCells('D3:E3'); 
        $objActSheet->setCellValue('D3', date("Y-m-d"));
        $objActSheet->getStyle('D3')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('D3')->getFont()->setSize(10);
        $objActSheet->getStyle('D3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('D3:E3')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('D3:E3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        
        $objActSheet->mergeCells('F3:H3'); 
        $objActSheet->setCellValue('F3', " INVOICE NO.");
        $objActSheet->getStyle('F3')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('F3')->getFont()->setSize(10);
        $objActSheet->getStyle('F3')->getFont()->setBold(True);
        $objActSheet->getStyle('F3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('F3:H3')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('F3:H3')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        
        $objActSheet->mergeCells('I3:J3'); 
        $objActSheet->setCellValue('I3', $billall->user->storeno."-".date("Ymd"));
        $objActSheet->getStyle('I3')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('I3')->getFont()->setSize(10);
        $objActSheet->getStyle('I3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('I3:J3')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getRowDimension('3')->setRowHeight(20);
        
        $objActSheet->mergeCells('A4:E4'); 
        $objActSheet->setCellValue('A4', " FROM (Shipper)  ");
        $objActSheet->getStyle('A4')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A4')->getFont()->setSize(10); 
        $objActSheet->getStyle('A4:E4')->getFont()->setBold(True);
        $objActSheet->getStyle('A4:E4')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('A4:E4')->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('F4:J4'); 
        $objActSheet->setCellValue('F4', " TO (Receiver)  ");
        $objActSheet->getStyle('F4')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('F4')->getFont()->setSize(10); 
        $objActSheet->getStyle('F4:J4')->getFont()->setBold(True);
        $objActSheet->getRowDimension('4')->setRowHeight(20);
        $objActSheet->getStyle('F4:J4')->getAlignment()->setWrapText(true);
        
        $strfrom = " No.3 Langshan 2nd Street,Area of Hi-Tech Industrial Zone \n Nanshan District, SHENZHEN \n GUANGDONG CHINA \n Contact:LuNa";
        $objActSheet->mergeCells('A5:E5'); 
        $objActSheet->setCellValue('A5', $strfrom);
        $objActSheet->getStyle('A5')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A5')->getFont()->setSize(10);
        $objActSheet->getStyle('A5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); 
        $objActSheet->getStyle('A5:E5')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('A5:E5')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('A5:E5')->getAlignment()->setWrapText(true);
        
        $strto = "  ".$invoiceall->address." \n  Zip code: ".$invoiceall->zipcode." \n  Tel: ".$invoiceall->tel." \n  Contact: ".$invoiceall->attn;
        $objActSheet->mergeCells('F5:J5'); 
        $objActSheet->setCellValue('F5', $strto);
        $objActSheet->getStyle('F5')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('F5')->getFont()->setSize(10);
        $objActSheet->getStyle('F5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); 
        $objActSheet->getStyle('F5:J5')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getRowDimension('5')->setRowHeight(100);
        $objActSheet->getStyle('F5:J5')->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('A6:E6'); 
        $objActSheet->setCellValue('A6', " COUNTRY OF EXPORT ");
        $objActSheet->getStyle('A6')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A6')->getFont()->setSize(10);
        $objActSheet->getStyle('A6')->getFont()->setBold(True);
        $objActSheet->getStyle('A6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        $objActSheet->getStyle('A6:E6')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('A6:E6')->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('F6:J6'); 
        $objActSheet->setCellValue('F6', " COUNTRY OF ULTIMATE DESTINATION ");
        $objActSheet->getStyle('F6')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('F6')->getFont()->setSize(10);
        $objActSheet->getStyle('F6')->getFont()->setBold(True);
        $objActSheet->getStyle('F6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        $objActSheet->getStyle('F6:J6')->getAlignment()->setWrapText(true);
        $objActSheet->getRowDimension('6')->setRowHeight(20);
        
        $objActSheet->mergeCells('A7:E7'); 
        $objActSheet->setCellValue('A7', " China");
        $objActSheet->getStyle('A7')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A7')->getFont()->setSize(10);
        $objActSheet->getStyle('A7')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        $objActSheet->getStyle('A7:E7')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('A7:E7')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('A7:E7')->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('F7:J7'); 
        $objActSheet->setCellValue('F7', $invoiceall->country);
        $objActSheet->getStyle('F7')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('F7')->getFont()->setSize(10);
        $objActSheet->getStyle('F7')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        $objActSheet->getStyle('F7:J7')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('F7:J7')->getAlignment()->setWrapText(true);
        $objActSheet->getRowDimension('7')->setRowHeight(20);
        
        $objActSheet->mergeCells('A8:E8'); 
        $objActSheet->setCellValue('A8', " REASON FOR EXPORT (e.g. personal gift, return for repair) ");
        $objActSheet->getStyle('A8')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A8')->getFont()->setSize(10);
        $objActSheet->getStyle('A8')->getFont()->setBold(True);
        $objActSheet->getStyle('A8')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        $objActSheet->getStyle('A8:E8')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('A8:E8')->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('F8:J8'); 
        $objActSheet->setCellValue('F8', " WEIGHT");
        $objActSheet->getStyle('F8')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('F8')->getFont()->setSize(10);
        $objActSheet->getStyle('F8')->getFont()->setBold(True);
        $objActSheet->getStyle('F8')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        $objActSheet->getStyle('F8:J8')->getAlignment()->setWrapText(true);
        $objActSheet->getRowDimension('8')->setRowHeight(20);
        
        $objActSheet->mergeCells('A9:E9'); 
        $objActSheet->setCellValue('A9', $invoiceall->reason);
        $objActSheet->getStyle('A9')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A9')->getFont()->setSize(10);
        $objActSheet->getStyle('A9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        $objActSheet->getStyle('A9:E9')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('A9:E9')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('A9:E9')->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('F9:J9'); 
        $objActSheet->setCellValue('F9', " ");
        $objActSheet->getStyle('F9')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('F9')->getFont()->setSize(10);
        $objActSheet->getStyle('F9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
        $objActSheet->getStyle('F9:J9')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('F9:J9')->getAlignment()->setWrapText(true);
        $objActSheet->getRowDimension('9')->setRowHeight(20);
        
        $objActSheet->setCellValue('A10', " NO. OF \n PKGS ");
        $objActSheet->getStyle('A10')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A10')->getFont()->setSize(10);
        $objActSheet->getStyle('A10')->getFont()->setBold(True);
        $objActSheet->getStyle('A10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('A10')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('A10')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('A10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('A10')->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('B10:D10'); 
        $objActSheet->setCellValue('B10', " DESCRIPTION OF GOODS ");
        $objActSheet->getStyle('B10')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('B10')->getFont()->setSize(10);
        $objActSheet->getStyle('B10:D10')->getFont()->setBold(True);
        $objActSheet->getStyle('B10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('B10')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('B10:D10')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('B10:D10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        
        $objActSheet->setCellValue('E10', " HS CODE ");
        $objActSheet->getStyle('E10')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('E10')->getFont()->setSize(10);
        $objActSheet->getStyle('E10')->getFont()->setBold(True);
        $objActSheet->getStyle('E10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('E10')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('E10')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        
        $objActSheet->mergeCells('F10:G10'); 
        $objActSheet->setCellValue('F10', " QTY ");
        $objActSheet->getStyle('F10')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('F10')->getFont()->setSize(10);
        $objActSheet->getStyle('F10')->getFont()->setBold(True);
        $objActSheet->getStyle('F10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('F10')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('F10:G10')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('F10:G10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        
        $objActSheet->mergeCells('H10:I10'); 
        $objActSheet->setCellValue('H10', " UNIT VALUE \n us$ ");
        $objActSheet->getStyle('H10')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('H10')->getFont()->setSize(10);
        $objActSheet->getStyle('H10')->getFont()->setBold(True);
        $objActSheet->getStyle('H10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('H10')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('H10:I10')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);  
        $objActSheet->getStyle('H10:I10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('H10:I10')->getAlignment()->setWrapText(true);
        
        $objActSheet->setCellValue('J10', " TOTAL \n VALUE ");
        $objActSheet->getStyle('J10')->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('J10')->getFont()->setSize(10);
        $objActSheet->getStyle('J10')->getFont()->setBold(True);
        $objActSheet->getStyle('J10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('J10')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
        $objActSheet->getStyle('J10')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
        $objActSheet->getStyle('J10')->getAlignment()->setWrapText(true);
        $objActSheet->getRowDimension('10')->setRowHeight(40);
        
        $i = 11;
        foreach ($invoicedetail as $valItem) {
            $pkgs = ($valItem->pkgs>0?$valItem->pkgs:"");
            $objActSheet->setCellValue('A'.$i, $pkgs);
            $objActSheet->getStyle('A'.$i)->getFont()->setName('Times New Roman');  
            $objActSheet->getStyle('A'.$i)->getFont()->setSize(10);
            $objActSheet->getStyle('A'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
            $objActSheet->getStyle('A'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  

            $objActSheet->mergeCells('B'.$i.":D".$i); 
            $objActSheet->setCellValue('B'.$i, $valItem->description);
            $objActSheet->getStyle('B'.$i)->getFont()->setName('Times New Roman');  
            $objActSheet->getStyle('B'.$i)->getFont()->setSize(10);
            $objActSheet->getStyle('B'.$i)->getAlignment()->setWrapText(true);
            $objActSheet->getStyle('B'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
            $objActSheet->getStyle('B'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  

            $objActSheet->setCellValue('E'.$i, $valItem->hscode);
            $objActSheet->getStyle('E'.$i)->getFont()->setName('Times New Roman');  
            $objActSheet->getStyle('E'.$i)->getFont()->setSize(10);
            $objActSheet->getStyle('E'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
            $objActSheet->getStyle('E'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  

            $objActSheet->mergeCells('F'.$i.":G".$i); 
            $objActSheet->setCellValue('F'.$i, $valItem->num);
            $objActSheet->getStyle('F'.$i)->getFont()->setName('Times New Roman');  
            $objActSheet->getStyle('F'.$i)->getFont()->setSize(10);
            $objActSheet->getStyle('F'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
            $objActSheet->getStyle('F'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  

            $objActSheet->mergeCells('H'.$i.":I".$i); 
            $objActSheet->setCellValue('H'.$i, $valItem->price);
            $objActSheet->getStyle('H'.$i)->getFont()->setName('Times New Roman');  
            $objActSheet->getStyle('H'.$i)->getFont()->setSize(10);
            $objActSheet->getStyle('H'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
            $objActSheet->getStyle('H'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  

            $objActSheet->setCellValue('J'.$i, $valItem->total);
            $objActSheet->getStyle('J'.$i)->getFont()->setName('Times New Roman');  
            $objActSheet->getStyle('J'.$i)->getFont()->setSize(10);
            $objActSheet->getStyle('J'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
            $objActSheet->getStyle('J'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
            $objActSheet->getRowDimension($i)->setRowHeight(30);
            $i++;
        }
        
        $objActSheet->mergeCells('A' . $i . ":H" . $i);
        $objActSheet->setCellValue('A' . $i, " ");
        $objActSheet->getStyle('A' . $i . ":H" . $i)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('A' . $i . ":H" . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        
        $objActSheet->mergeCells('I' . $i . ":J" . $i);
        $objActSheet->setCellValue('I' . $i, " TOTAL INVOICE \n VALUE ");
        $objActSheet->getStyle('I' . $i)->getFont()->setName('Times New Roman');
        $objActSheet->getStyle('I' . $i)->getFont()->setSize(10);
        $objActSheet->getStyle('I' . $i)->getFont()->setBold(True);
        $objActSheet->getStyle('I' . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('I' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('I' . $i . ":J" . $i)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('I' . $i . ":J" . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('I' . $i . ":J" . $i)->getAlignment()->setWrapText(true);
        $objActSheet->getRowDimension($i)->setRowHeight(40);
        $i++;
        
        $objActSheet->mergeCells('A' . $i . ":H" . $i);
        $objActSheet->setCellValue('A' . $i, " ");
        $objActSheet->getStyle('A' . $i . ":H" . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('A' . $i . ":H" . $i)->getBorders()->getBottom()->getColor()->setARGB('FFFFFF');
        
        $objActSheet->mergeCells('I' . $i . ":J" . $i);
        $objActSheet->setCellValue('I' . $i, "$".$invoiceall->total);
        $objActSheet->getStyle('I' . $i)->getFont()->setName('Times New Roman');
        $objActSheet->getStyle('I' . $i)->getFont()->setSize(10);
        $objActSheet->getStyle('I' . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('I' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('I' . $i . ":J" . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getRowDimension($i)->setRowHeight(40);
        $i++;
        
        $objActSheet->mergeCells('A' . $i . ":H" . $i);
        $objActSheet->setCellValue('A' . $i , " I DECLARE ALL THE INFORMATION CONTAINED IN THE INVOICE TO BE TRUE AND CORRECT.\n\n SIGNATURE OF SHIPPER / EXPORTER");
        $objActSheet->getStyle('A' . $i )->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A' . $i )->getFont()->setSize(10);
        $objActSheet->getStyle('A' . $i)->getFont()->setBold(True);
        $objActSheet->getStyle('A' . $i . ":H" . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('A' . $i . ":H" . $i)->getBorders()->getBottom()->getColor()->setARGB('FFFFFF');
        $objActSheet->getStyle('A' . $i)->getAlignment()->setWrapText(true);
        
        $objActSheet->mergeCells('I' . $i . ":J" . $i);
        $objActSheet->setCellValue('I' . $i, "\n\n DATE ");
        $objActSheet->getStyle('I' . $i)->getFont()->setName('Times New Roman');
        $objActSheet->getStyle('I' . $i)->getFont()->setSize(10);
        $objActSheet->getStyle('I' . $i)->getFont()->setBold(True);
        $objActSheet->getStyle('I' . $i )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('I' . $i )->getBorders()->getLeft()->getColor()->setARGB('FFFFFF');
        $objActSheet->getStyle('I' . $i. ":J" . $i )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('I' . $i. ":J" . $i )->getBorders()->getBottom()->getColor()->setARGB('FFFFFF');
        $objActSheet->getStyle('I' . $i. ":J" . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('I' . $i. ":J" . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);
        $objActSheet->getStyle('I' . $i)->getAlignment()->setWrapText(true);
        $objActSheet->getRowDimension($i)->setRowHeight(50);
        $i++;
        
        $objActSheet->setCellValue('A' . $i , " UUCH ");
        $objActSheet->getStyle('A' . $i )->getFont()->setName('Times New Roman');  
        $objActSheet->getStyle('A' . $i )->getFont()->setSize(10);
        $objActSheet->getStyle('A' . $i )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('A' . $i )->getBorders()->getRight()->getColor()->setARGB('FFFFFF');
        $objActSheet->getStyle('A' . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('A' . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('A' . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        
        $objActSheet->mergeCells('B' . $i . ":H" . $i);
        $objActSheet->setCellValue('B' . $i, " ");
//        $objActSheet->getStyle('B' . $i . ":I" . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//        $objActSheet->getStyle('B' . $i . ":I" . $i)->getBorders()->getBottom()->getColor()->setARGB('FFFFFF');
        
        $objActSheet->mergeCells('I' . $i . ":J" . $i);
        $objActSheet->setCellValue('I' . $i, date("Y-m-d"));
        $objActSheet->getStyle('I' . $i)->getFont()->setName('Times New Roman');
        $objActSheet->getStyle('I' . $i)->getFont()->setSize(10);
        $objActSheet->getStyle('I' . $i )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('I' . $i )->getBorders()->getLeft()->getColor()->setARGB('FFFFFF');
        $objActSheet->getStyle('I' . $i . ":J" . $i)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        $objActSheet->getStyle('I' . $i . ":J" . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getStyle('I' . $i . ":J" . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getRowDimension($i)->setRowHeight(40);
        
        $outputFileName = $billall->billNo.".xlsx"; 
        header("Content-Type: application/force-download");  
        header("Content-Type: application/octet-stream");  
        header("Content-Type: application/download");  
        header('Content-Disposition:inline;filename="'.$outputFileName.'"');  
        header("Content-Transfer-Encoding: binary");  
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");  
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");  
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
        header("Pragma: no-cache");  
        $objWriter->save('php://output');
        return 1;
    }
    
    public static function printEub($id)
    {
        $billall = Application_Model_wayBillItem::load($id);
        
    }
}
?>
